﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using WeifenLuo.WinFormsUI.Docking;
using Infragistics.Win;
using Infragistics.Win.UltraWinGrid;
using Infragistics.Excel;
namespace BYManageInfoSystem
{
    public partial class Frm_FinFacCheckSheetNew : DockContent
    {
        private List<string> m_lstSpecialFacID = new List<string>();
        private string m_strFolder = "d:/财务工厂对账单/";
        private DGBY.BLL.BiZhiTab m_bllBiZhiTab = new DGBY.BLL.BiZhiTab();
        private DGBY.BLL.FactoryDataTab m_bllFactoryDataTab = new DGBY.BLL.FactoryDataTab();
        private DGBY.BLL.BGCKHTTab m_bllBGCKHTTab = new DGBY.BLL.BGCKHTTab();
        private DGBY.BLL.FinCheckInvoiceRecordDetailTab_New m_bllFinCheckInvoiceRecordDetailTab_New = new DGBY.BLL.FinCheckInvoiceRecordDetailTab_New();
        private int m_nFacId = 0;
       // private DGBY.BLL.FinChuKouJinETab_New m_bllFinChuKouJinETab_New = new DGBY.BLL.FinChuKouJinETab_New();
        private DGBY.BLL.FinRecvMoneyDetailTab_New m_bllFinRecvMoneyDetailTab_New = new DGBY.BLL.FinRecvMoneyDetailTab_New();
     //   private bool m_bIsRecvMoneyMoreThanChuKouJinE = false;
        private string m_strShenbaoDateCondition = @"CASE WHEN 
DATEDIFF(day, HxdNoTab.SBDate, 1900/01/01) < 0 
THEN  HxdNoTab.SBDate 
else HxdNoTab.TongJDate
end";
        public Frm_FinFacCheckSheetNew()
        {
            InitializeComponent();
        }
        private void InitCombox()
        {
            DataSet ds = m_bllFactoryDataTab.GetList("");
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                this.comboBoxFac.DataSource = ds.Tables[0];

                this.comboBoxFac.ValueMember = "FactoryDataTabID";
                this.comboBoxFac.DisplayMember = "工厂简称";
                this.comboBoxFac.AutoCompleteSource = AutoCompleteSource.ListItems;
                this.comboBoxFac.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
                this.comboBoxFac.SelectedIndex = 0;
            }
        }
        /// 取得某月的第一天
        /// </summary>
        /// <param name="datetime">要取得月份第一天的时间</param>
        /// <returns></returns>
        private DateTime FirstDayOfMonth(DateTime datetime)
        {
            return datetime.AddDays(1 - datetime.Day);
        }
        /// <summary>
        /// 取得某月的最后一天
        /// </summary>
        /// <param name="datetime">要取得月份最后一天的时间</param>
        /// <returns></returns>
        private DateTime LastDayOfMonth(DateTime datetime)
        {
            return datetime.AddDays(1 - datetime.Day).AddMonths(1).AddDays(-1);
        }

        private Decimal ComputeRevMoneyByDate(string strWhere, string strFac)
        {
            Decimal dtTotal = 0;
            DataSet ds = m_bllFinRecvMoneyDetailTab_New.GetList("z1.不参与对账 = 0 and " + strWhere);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Decimal dtTemp = 0;
             

                    Decimal dtYuanBi = (Decimal)ds.Tables[0].Rows[i]["原币金额"];
                    Decimal dtKouFeeTotal = (Decimal)ds.Tables[0].Rows[i]["手续费"] 
                        + (Decimal)ds.Tables[0].Rows[i]["利息"]
                         + (Decimal)ds.Tables[0].Rows[i]["报关及拖车费"]
                          + (Decimal)ds.Tables[0].Rows[i]["其他费用"];
                    Decimal dtRmbHuilv = (Decimal)ds.Tables[0].Rows[i]["RMB汇率"];
                    Decimal dtYiJieHui = 0;
                    
                    if(dtRmbHuilv > 0)
                        dtYiJieHui = dtYuanBi - dtKouFeeTotal / dtRmbHuilv;

                    if (!m_lstSpecialFacID.Contains(strFac))
                    {
                        dtTemp = dtYiJieHui * (Decimal)ds.Tables[0].Rows[i]["汇率"];
                    }
                    else
                    {
                        dtTemp = dtYiJieHui;
                    }
                    dtTotal += dtTemp;
                    //if (!m_lstSpecialFac.Contains(this.comboBoxFac.SelectedText))
                    //{
                    //    dtTemp = (Decimal)ds.Tables[0].Rows[i]["扣费后折算为USD"];
                  
                    //}
                    //else
                    //{
                    //    if ((Decimal)ds.Tables[0].Rows[i]["汇率"] != 0)
                    //        //dtTemp = (Decimal)ds.Tables[0].Rows[i]["扣费后折算为USD"] / (Decimal)ds.Tables[0].Rows[i]["汇率"];
                    //        dtTemp = Convert.ToDecimal((((Decimal)ds.Tables[0].Rows[i]["扣费后折算为USD"] / (Decimal)ds.Tables[0].Rows[i]["汇率"])).ToString("f2"));
                    //    else
                    //        dtTemp = 0;
                    //}


                    
                }
            }
            return dtTotal;
        }

        private Decimal ComputeChuKouJinEByDate(string strWhere)
        {
            Decimal dtTotal = 0;
            DataSet ds = m_bllBGCKHTTab.GetChuKouRecordTabInfo(strWhere + " and PaidanRegisterTab.PaidanRegisterTabID in (select HxdNoTab.PaidanRegisterTabID from HxdNoTab)" );
            //DataSet ds = m_bllBGCKHTTab.GetChuKouRecordTabInfo(strWhere);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                  
                    Decimal dtTemp = (Decimal)ds.Tables[0].Rows[i]["报关金额"];
                    dtTotal += dtTemp;
                }
            }
            return dtTotal;
        }

        private void buttonQuery_Click(object sender, EventArgs e)
        {
            if (null != this.comboBoxFac.SelectedValue)
            {
                m_nFacId = (int)this.comboBoxFac.SelectedValue;
                ShowAllRecordInDataGridView(m_nFacId.ToString());
            }    
        }

        private void AddNewColumn(DataTable dt)
        {
            int nIndex = 0;

            dt.Columns.Add("合同号", System.Type.GetType("System.String"));
            dt.Columns["合同号"].SetOrdinal(nIndex++);
            dt.Columns.Add("出货日期", System.Type.GetType("System.DateTime"));
            dt.Columns["出货日期"].SetOrdinal(nIndex++);
            dt.Columns.Add("柜号", System.Type.GetType("System.String"));
            dt.Columns["柜号"].SetOrdinal(nIndex++);
            dt.Columns.Add("报关金额", System.Type.GetType("System.Decimal"));
            dt.Columns["报关金额"].SetOrdinal(nIndex++);
            dt.Columns.Add("币种", System.Type.GetType("System.String"));
            dt.Columns["币种"].SetOrdinal(nIndex++);
            dt.Columns.Add("结汇日期", System.Type.GetType("System.DateTime"));
            dt.Columns["结汇日期"].SetOrdinal(nIndex++);
            dt.Columns.Add("已结汇", System.Type.GetType("System.Decimal"));
            dt.Columns["已结汇"].SetOrdinal(nIndex++);
            dt.Columns.Add("折算率", System.Type.GetType("System.Decimal"));
            dt.Columns["折算率"].SetOrdinal(nIndex++);
            dt.Columns.Add("折算原币", System.Type.GetType("System.Decimal"));
            dt.Columns["折算原币"].SetOrdinal(nIndex++);
            dt.Columns.Add("结汇余额(USD)", System.Type.GetType("System.Decimal"));
            dt.Columns["结汇余额(USD)"].SetOrdinal(nIndex++);

        }
        private void GetAllSpecFacID()
        {
            DataSet ds = m_bllFactoryDataTab.GetList("bitFiledReserv1 > 0");
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for(int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string strSpecFacID = ds.Tables[0].Rows[i]["FactoryDataTabID"].ToString();
                    m_lstSpecialFacID.Add(strSpecFacID);
                }
            }

        }
        private void ShowAllRecordInDataGridViewBatch(string strFac)
        {
            //            string strShenbaoDateCondition = @"CASE WHEN 
            //DATEDIFF(day, HxdNoTab.SBDate, 1900/01/01) < 0 
            //THEN  HxdNoTab.SBDate 
            //else BGCKHTTab.QDDate 
            //end";
            // DateTime dtFirstDayTemp = FirstDayOfMonth(this.dateTimePickerFrom.Value);
            // DateTime dtLastDayTemp = LastDayOfMonth(this.dateTimePickerFrom.Value);
            // m_bIsRecvMoneyMoreThanChuKouJinE = false;
            DataSet dsFac = m_bllFactoryDataTab.GetList(" FactoryDataTab.FactoryDataTabID = '" + strFac + "'");
            Decimal dtFirstJinE = (Decimal)dsFac.Tables[0].Rows[0]["出口与结汇期初余额"];
            Decimal dtChuKouTotalFrom = ComputeChuKouJinEByDate(" FactoryDataTab.FactoryDataTabID = '" + strFac + "'"
                                    + " and " + m_strShenbaoDateCondition + " < " + "'" + this.dateTimePickerFrom.Value.Date + "'");

            Decimal dtRecvMoneyTotalFrom = ComputeRevMoneyByDate(" 客户 = '" + strFac + "'"
                                    + " and 结汇日期 < " + "'" + this.dateTimePickerFrom.Value.Date + "'", strFac);

            Decimal dtDiffFrom = dtFirstJinE + dtChuKouTotalFrom - dtRecvMoneyTotalFrom;
 

            Decimal dtChuKouTotalTo = ComputeChuKouJinEByDate(" FactoryDataTab.FactoryDataTabID = '" + strFac + "'"
                        + " and " + m_strShenbaoDateCondition + " <= " + "'" + this.dateTimePickerTo.Value.Date + "'");

            Decimal dtRecvMoneyTotalTo = ComputeRevMoneyByDate(" 客户 = '" + strFac + "'"
                                    + " and 结汇日期 <= " + "'" + this.dateTimePickerTo.Value.Date + "'", strFac);



            Decimal dtChouKouTotal = dtChuKouTotalTo - dtChuKouTotalFrom;

            Decimal dtRecvMoneyTotal = dtRecvMoneyTotalTo - dtRecvMoneyTotalFrom;


            Decimal dtDiffTo = dtDiffFrom + dtChouKouTotal - dtRecvMoneyTotal;


            int nMaxCountLines = 0;
            int nWholeRecIndex = 0;
            DataTable dtNew = new DataTable();
            AddNewColumn(dtNew);
            ultraGrid1.DataSource = null;
            DataSet ds
                = m_bllBGCKHTTab.GetChuKouRecordTabInfo(" FactoryDataTab.FactoryDataTabID = '" + strFac + "'"
                                    + " and " + m_strShenbaoDateCondition + " >= " + "'" + this.dateTimePickerFrom.Value.Date + "'"
                                    + " and " + m_strShenbaoDateCondition + " <= " + "'" + this.dateTimePickerTo.Value.Date + "'"
                                    + " and PaidanRegisterTab.PaidanRegisterTabID in (select HxdNoTab.PaidanRegisterTabID from HxdNoTab) ");

            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                if (nMaxCountLines < ds.Tables[0].Rows.Count)
                    nMaxCountLines = ds.Tables[0].Rows.Count;

            }

            DataSet ds2 = m_bllFinRecvMoneyDetailTab_New.GetListBiZhiString(" 客户 = '" + strFac + "'"
                                    + " and 结汇日期 >= " + "'" + this.dateTimePickerFrom.Value.Date + "'"
                                    + " and 结汇日期 <= " + "'" + this.dateTimePickerTo.Value.Date + "'");
            if (ds2.Tables.Count > 0 && ds2.Tables[0].Rows.Count > 0)
            {
                if (nMaxCountLines < ds2.Tables[0].Rows.Count)
                    nMaxCountLines = ds2.Tables[0].Rows.Count;
            }

            for (int i = 0; i < nMaxCountLines; i++)
            {
                DataRow dr = dtNew.NewRow();
                dtNew.Rows.Add(dr);
            }

            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {

                    dtNew.Rows[nWholeRecIndex]["合同号"] = ds.Tables[0].Rows[i]["合同号"];
                    dtNew.Rows[nWholeRecIndex]["出货日期"] = ds.Tables[0].Rows[i]["申报日期"];
                    dtNew.Rows[nWholeRecIndex]["柜号"] = ds.Tables[0].Rows[i]["集装箱柜号"];
                    dtNew.Rows[nWholeRecIndex]["报关金额"] = ds.Tables[0].Rows[i]["报关金额"];


                    nWholeRecIndex++;
                }
            }

            nWholeRecIndex = 0;
            if (ds2.Tables.Count > 0 && ds2.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
                {
                    dtNew.Rows[nWholeRecIndex]["币种"] = ds2.Tables[0].Rows[i]["币种名称"];
                    dtNew.Rows[nWholeRecIndex]["结汇日期"] = ds2.Tables[0].Rows[i]["结汇日期"];

                    Decimal dtYuanBi = (Decimal)ds2.Tables[0].Rows[i]["原币金额"];
                    Decimal dtKouFeeTotal = (Decimal)ds2.Tables[0].Rows[i]["手续费"]
                        + (Decimal)ds2.Tables[0].Rows[i]["利息"]
                         + (Decimal)ds2.Tables[0].Rows[i]["报关及拖车费"]
                          + (Decimal)ds2.Tables[0].Rows[i]["其他费用"];
                    Decimal dtRmbHuilv = (Decimal)ds2.Tables[0].Rows[i]["RMB汇率"];

                    if (dtRmbHuilv > 0)
                        dtNew.Rows[nWholeRecIndex]["已结汇"] = dtYuanBi - dtKouFeeTotal / dtRmbHuilv;
                    else
                        dtNew.Rows[nWholeRecIndex]["已结汇"] = 0;
                    Decimal dtTemp = (Decimal)ds2.Tables[0].Rows[i]["汇率"];
                    if (!m_lstSpecialFacID.Contains(strFac))
                    {
                        dtNew.Rows[nWholeRecIndex]["折算率"] = dtTemp.ToString("f6");
                        if (dtRmbHuilv > 0)
                            dtNew.Rows[nWholeRecIndex]["折算原币"] = dtTemp * (dtYuanBi - dtKouFeeTotal / dtRmbHuilv);
                        else
                            dtNew.Rows[nWholeRecIndex]["折算原币"] = 0;
                    }
                    else
                    {
                        dtNew.Rows[nWholeRecIndex]["折算率"] = 1;
                        if (dtRmbHuilv > 0)
                            dtNew.Rows[nWholeRecIndex]["折算原币"] = (dtYuanBi - dtKouFeeTotal / dtRmbHuilv);
                        else
                            dtNew.Rows[nWholeRecIndex]["折算原币"] = 0;
                    }
                    
                    //dtNew.Rows[nWholeRecIndex]["币种"] = ds2.Tables[0].Rows[i]["币种名称"];
                    //dtNew.Rows[nWholeRecIndex]["结汇日期"] = ds2.Tables[0].Rows[i]["结汇日期"];
                    //dtNew.Rows[nWholeRecIndex]["已结汇"] = ds2.Tables[0].Rows[i]["扣费后折算为USD"];

                    //dtNew.Rows[nWholeRecIndex]["折算率"] = ds2.Tables[0].Rows[i]["汇率"];
                    //dtNew.Rows[nWholeRecIndex]["折算原币"] = ds2.Tables[0].Rows[i]["扣费后折算为USD"];
                    //    dtNew.Rows[nWholeRecIndex]["结汇余额(USD)"] = ds2.Tables[0].Rows[i]["扣费后折算为USD"];
                    nWholeRecIndex++;
                }
            }

            DataRow drTotal = dtNew.NewRow();
            dtNew.Rows.Add(drTotal);
            dtNew.Rows[nMaxCountLines]["合同号"] = "合计";
            dtNew.Rows[nMaxCountLines]["报关金额"] = dtChouKouTotal;
            dtNew.Rows[nMaxCountLines]["折算原币"] = dtRecvMoneyTotal;
            dtNew.Rows[nMaxCountLines]["结汇余额(USD)"] = dtDiffTo;


            ultraGrid1.DataSource = dtNew;
            this.ultraGrid1.DisplayLayout.Bands[0].Columns["柜号"].CellMultiLine = Infragistics.Win.DefaultableBoolean.True;

        }
        private void ShowAllRecordInDataGridView(string strFac)
        {
//            string strShenbaoDateCondition = @"CASE WHEN 
//DATEDIFF(day, HxdNoTab.SBDate, 1900/01/01) < 0 
//THEN  HxdNoTab.SBDate 
//else BGCKHTTab.QDDate 
//end";
           // DateTime dtFirstDayTemp = FirstDayOfMonth(this.dateTimePickerFrom.Value);
           // DateTime dtLastDayTemp = LastDayOfMonth(this.dateTimePickerFrom.Value);
           // m_bIsRecvMoneyMoreThanChuKouJinE = false;
            DataSet dsFac = m_bllFactoryDataTab.GetList(" FactoryDataTab.FactoryDataTabID = '" + strFac + "'");
            Decimal dtFirstJinE = (Decimal)dsFac.Tables[0].Rows[0]["出口与结汇期初余额"];

            Decimal dtChuKouTotalFrom = ComputeChuKouJinEByDate(" FactoryDataTab.FactoryDataTabID = '" + strFac + "'"
                                    + " and " + m_strShenbaoDateCondition + " < " + "'" + this.dateTimePickerFrom.Value.Date + "'");

            Decimal dtRecvMoneyTotalFrom = ComputeRevMoneyByDate(" 客户 = '" + strFac + "'"
                                    + " and 结汇日期 < " + "'" + this.dateTimePickerFrom.Value.Date + "'", strFac);

            Decimal dtDiffFrom = dtFirstJinE + dtChuKouTotalFrom - dtRecvMoneyTotalFrom;
            this.textBoxLastJinE.Text = dtDiffFrom.ToString();

            Decimal dtChuKouTotalTo = ComputeChuKouJinEByDate(" FactoryDataTab.FactoryDataTabID = '" + strFac + "'"
                        + " and " + m_strShenbaoDateCondition + " <= " + "'" + this.dateTimePickerTo.Value.Date + "'");

            Decimal dtRecvMoneyTotalTo = ComputeRevMoneyByDate(" 客户 = '" + strFac + "'"
                                    + " and 结汇日期 <= " + "'" + this.dateTimePickerTo.Value.Date + "'", strFac);



            Decimal dtChouKouTotal = dtChuKouTotalTo - dtChuKouTotalFrom;
            this.textBoxChuKouTotal.Text = dtChouKouTotal.ToString();
            Decimal dtRecvMoneyTotal = dtRecvMoneyTotalTo - dtRecvMoneyTotalFrom;


            //dtRecvMoneyTotal = ComputeRevMoneyByDate(" 客户 = '" + strFac + "'"
            //                        + " and 结汇日期 >= " + "'" + this.dateTimePickerFrom.Value.Date + "'"
            //                        + " and 结汇日期 <= " + "'" + this.dateTimePickerTo.Value.Date + "'");

            this.textBoxRecvMoneyTotal.Text = dtRecvMoneyTotal.ToString();

            Decimal dtDiffTo = dtDiffFrom + dtChouKouTotal - dtRecvMoneyTotal;
            this.textBoxBetween.Text = dtDiffTo.ToString();
            //if (dtDiffTo >= 0)
            //    m_bIsRecvMoneyMoreThanChuKouJinE = true;

            int nMaxCountLines = 0;
            int nWholeRecIndex = 0;
            DataTable dtNew = new DataTable();
            AddNewColumn(dtNew);
            ultraGrid1.DataSource = null;
            DataSet ds
                = m_bllBGCKHTTab.GetChuKouRecordTabInfo(" FactoryDataTab.FactoryDataTabID = '" + strFac + "'"
                                    + " and " + m_strShenbaoDateCondition + " >= " + "'" + this.dateTimePickerFrom.Value.Date + "'"
                                    + " and " + m_strShenbaoDateCondition + " <= " + "'" + this.dateTimePickerTo.Value.Date + "'"
                                    + " and PaidanRegisterTab.PaidanRegisterTabID in (select HxdNoTab.PaidanRegisterTabID from HxdNoTab) ");
           
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                if (nMaxCountLines < ds.Tables[0].Rows.Count)
                    nMaxCountLines = ds.Tables[0].Rows.Count;
                //for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                //{
                //    DataRow dr = dtNew.NewRow();
                //    dtNew.Rows[nWholeRecIndex]["合同号"] = ds.Tables[0].Rows[i]["合同号"];
                //    dtNew.Rows[nWholeRecIndex]["出货日期"] = ds.Tables[0].Rows[i]["申报日期"];
                //  //  dtNew.Rows[nWholeRecIndex]["柜号"] = ds.Tables[0].Rows[i]["合同号"];
                //    dtNew.Rows[nWholeRecIndex]["报关金额"] = ds.Tables[0].Rows[i]["报关金额"];
                //    dtNew.Rows[nWholeRecIndex]["币种"] = ds.Tables[0].Rows[i]["币制"];
                //    dtNew.Rows.Add(dr);
                //    nWholeRecIndex++;
                //}
            }

            DataSet ds2 = m_bllFinRecvMoneyDetailTab_New.GetListBiZhiString(" 客户 = '" + strFac + "'"
                                    + " and 结汇日期 >= " + "'" + this.dateTimePickerFrom.Value.Date + "'"
                                    + " and 结汇日期 <= " + "'" + this.dateTimePickerTo.Value.Date + "'");
            if (ds2.Tables.Count > 0 && ds2.Tables[0].Rows.Count > 0)
            {
                if (nMaxCountLines < ds2.Tables[0].Rows.Count)
                    nMaxCountLines = ds2.Tables[0].Rows.Count;
            }

            for (int i = 0; i < nMaxCountLines; i++)
            {
                DataRow dr = dtNew.NewRow();
                dtNew.Rows.Add(dr);
            }

            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                   
                    dtNew.Rows[nWholeRecIndex]["合同号"] = ds.Tables[0].Rows[i]["合同号"];
                    dtNew.Rows[nWholeRecIndex]["出货日期"] = ds.Tables[0].Rows[i]["申报日期"];
                    dtNew.Rows[nWholeRecIndex]["柜号"] = ds.Tables[0].Rows[i]["集装箱柜号"];
                    dtNew.Rows[nWholeRecIndex]["报关金额"] = ds.Tables[0].Rows[i]["报关金额"];
              
                  
                    nWholeRecIndex++;
                }
            }
   
            nWholeRecIndex = 0;
            if (ds2.Tables.Count > 0 && ds2.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
                {
                    dtNew.Rows[nWholeRecIndex]["币种"] = ds2.Tables[0].Rows[i]["币种名称"];
                    dtNew.Rows[nWholeRecIndex]["结汇日期"] = ds2.Tables[0].Rows[i]["结汇日期"];

                    Decimal dtYuanBi = (Decimal)ds2.Tables[0].Rows[i]["原币金额"];
                    Decimal dtKouFeeTotal = (Decimal)ds2.Tables[0].Rows[i]["手续费"]
                        + (Decimal)ds2.Tables[0].Rows[i]["利息"]
                         + (Decimal)ds2.Tables[0].Rows[i]["报关及拖车费"]
                          + (Decimal)ds2.Tables[0].Rows[i]["其他费用"];
                    Decimal dtRmbHuilv = (Decimal)ds2.Tables[0].Rows[i]["RMB汇率"];

                    if (dtRmbHuilv > 0)
                        dtNew.Rows[nWholeRecIndex]["已结汇"] = dtYuanBi - dtKouFeeTotal / dtRmbHuilv;
                    else
                        dtNew.Rows[nWholeRecIndex]["已结汇"] = 0;

                    Decimal dtTemp = (Decimal)ds2.Tables[0].Rows[i]["汇率"];

                    if (!m_lstSpecialFacID.Contains(strFac))
                    {
                        dtNew.Rows[nWholeRecIndex]["折算率"] = dtTemp.ToString("f6");
                        if (dtRmbHuilv > 0)
                            dtNew.Rows[nWholeRecIndex]["折算原币"] = dtTemp * (dtYuanBi - dtKouFeeTotal / dtRmbHuilv);
                        else
                            dtNew.Rows[nWholeRecIndex]["折算原币"] = 0;
                    }
                    else
                    {
                        dtNew.Rows[nWholeRecIndex]["折算率"] = 1;
                        if (dtRmbHuilv > 0)
                            dtNew.Rows[nWholeRecIndex]["折算原币"] = (dtYuanBi - dtKouFeeTotal / dtRmbHuilv);
                        else
                            dtNew.Rows[nWholeRecIndex]["折算原币"] = 0;
                    }

                    nWholeRecIndex++;
                }
            }

            DataRow drTotal = dtNew.NewRow();
            dtNew.Rows.Add(drTotal);
            dtNew.Rows[nMaxCountLines]["合同号"] = "合计";
            dtNew.Rows[nMaxCountLines]["报关金额"] = dtChouKouTotal;
            dtNew.Rows[nMaxCountLines]["折算原币"] = dtRecvMoneyTotal;
            dtNew.Rows[nMaxCountLines]["结汇余额(USD)"] = dtDiffTo;


            ultraGrid1.DataSource = dtNew;
            this.ultraGrid1.DisplayLayout.Bands[0].Columns["柜号"].CellMultiLine = Infragistics.Win.DefaultableBoolean.True;

        }

        private void Frm_FinFacCheckSheetNew_Load(object sender, EventArgs e)
        {
            InitCombox();
            GetAllSpecFacID();
            //m_lstSpecialFac.Add("亚岑");
            //m_lstSpecialFac.Add("成伟");
            //m_lstSpecialFac.Add("威乐");
        }

        private void ultraGridExcelExporter1_BeginExport(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.BeginExportEventArgs e)
        {
            DataSet ds = m_bllFactoryDataTab.GetList("FactoryDataTab.FactoryDataTabID = '" + m_nFacId.ToString() + "'");
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                string strFacAbbr = ds.Tables[0].Rows[0]["工厂简称"].ToString();
                string strFacName = ds.Tables[0].Rows[0]["工厂名称"].ToString();

                DateTime dtFirst = FirstDayOfMonth(this.dateTimePickerFrom.Value);
                string strYear = dtFirst.Year.ToString();
                string strMonth = dtFirst.Month.ToString();

                Worksheet ws = e.CurrentWorksheet;
                ws.Rows[1].Cells[4].Value = strYear + "年" + strMonth + "月" + strFacAbbr + "的对账单";
                ws.Rows[2].Cells[0].Value = strFacName;
                ws.Rows[3].Cells[0].Value = "明细如下：";

                e.CurrentRowIndex = 4;
            }
        }

        private void ultraGridExcelExporter1_EndExport(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.EndExportEventArgs e)
        {
            string strFacGoods = "";
            string strCompName = "";
            Decimal dtFirstJinE = 0;
            DataSet ds = m_bllFactoryDataTab.GetList("FactoryDataTab.FactoryDataTabID = '" + m_nFacId.ToString() + "'");
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                strFacGoods = ds.Tables[0].Rows[0]["货品种类"].ToString();
                strCompName = ds.Tables[0].Rows[0]["公司名称"].ToString();
                dtFirstJinE = (Decimal)ds.Tables[0].Rows[0]["出口与结汇期初余额"];
            }
            DateTime dtFirstDayTemp = FirstDayOfMonth(this.dateTimePickerFrom.Value);
            DateTime dtLastDayTemp = LastDayOfMonth(this.dateTimePickerFrom.Value);

            Decimal dtChuKouTotalFrom = ComputeChuKouJinEByDate(" FactoryDataTab.FactoryDataTabID = '" + m_nFacId.ToString() + "'"
                                    + " and " + m_strShenbaoDateCondition + " < " + "'" + dtFirstDayTemp.Date + "'");

            Decimal dtRecvMoneyTotalFrom = ComputeRevMoneyByDate(" 客户 = '" + m_nFacId.ToString() + "'"
                                    + " and 结汇日期 < " + "'" + dtFirstDayTemp.Date + "'", m_nFacId.ToString());

            Decimal dtDiffFrom = dtFirstJinE + dtChuKouTotalFrom - dtRecvMoneyTotalFrom;

            Decimal dtChuKouTotalTo = ComputeChuKouJinEByDate(" FactoryDataTab.FactoryDataTabID = '" + m_nFacId.ToString() + "'"
                        + " and " + m_strShenbaoDateCondition + " <= " + "'" + dtLastDayTemp.Date + "'");

            Decimal dtRecvMoneyTotalTo = ComputeRevMoneyByDate(" 客户 = '" + m_nFacId.ToString() + "'"
                                    + " and 结汇日期 <= " + "'" + dtLastDayTemp.Date + "'", m_nFacId.ToString());

         
            Decimal dtChouKouTotal = dtChuKouTotalTo - dtChuKouTotalFrom;

            Decimal dtRecvMoneyTotal = dtRecvMoneyTotalTo - dtRecvMoneyTotalFrom;
 
            Decimal dtDiffTo = dtDiffFrom + dtChouKouTotal - dtRecvMoneyTotal;

            Worksheet ws = e.CurrentWorksheet;

            int a = e.CurrentRowIndex;
            DateTime dtFirst = FirstDayOfMonth(this.dateTimePickerFrom.Value);
            string strYear = dtFirst.Year.ToString();
            string strMonth = dtFirst.Month.ToString();
            
            ws.Rows[a + 0].Cells[8].Value = "币种";
            ws.Rows[a + 0].Cells[9].Value = "金额";

            ws.Rows[a + 1].Cells[0].Value = "1.期初余额:";
            ws.Rows[a + 1].Cells[8].Value = "USD";
            ws.Rows[a + 1].Cells[9].Value = dtDiffFrom;

            ws.Rows[a + 2].Cells[0].Value = "2."+ strYear + "年" + strMonth + "月" + "贵公司出口"+ strFacGoods + "一批,出口额";
            ws.Rows[a + 2].Cells[8].Value = "USD";
            ws.Rows[a + 2].Cells[9].Value = dtChouKouTotal;


            ws.Rows[a + 3].Cells[0].Value = "3." + strYear + "年" + strMonth + "月" + "收到贵司结汇金额";
            ws.Rows[a + 3].Cells[8].Value = "USD";
            ws.Rows[a + 3].Cells[9].Value = dtRecvMoneyTotal;


            ws.Rows[a + 4].Cells[0].Value = "4.因此贵司差额为=1+2-3";
            ws.Rows[a + 4].Cells[8].Value = "USD";
            ws.Rows[a + 4].Cells[9].Value = dtDiffTo;

            ws.Rows[a + 5].Cells[0].Value = "注意:";
            ws.Rows[a + 6].Cells[0].Value = "1.结汇余额是正数是代表贵公司还没有结汇完毕,请贵司尽快将货款结汇到我司指定帐上,谢谢.";
            ws.Rows[a + 7].Cells[0].Value = "2.结汇余额是是负数时代表贵司多结汇,请贵司尽快出货,谢谢.";

            ws.Rows[a + 8].Cells[0].Value = "联系人:梁小姐";
            ws.Rows[a + 8].Cells[7].Value = strCompName;

            ws.Rows[a + 9].Cells[0].Value = "联系电话:0769-22421328转810或13600266657";
            ws.Rows[a + 9].Cells[7].Value = System.DateTime.Now.Date;
        }

        private void buttonExportToExcel_Click(object sender, EventArgs e)
        {
            DataTable dt = (DataTable)ultraGrid1.DataSource;

            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    SaveFileDialog sfd = new SaveFileDialog();
                    sfd.Filter = "Excel文件(*.xls)|*.xls";
                    string outFile = "";

                    if (sfd.ShowDialog() == DialogResult.OK)
                    {
                        outFile = sfd.FileName;
                    }
                    else
                    {
                        return;
                    }

                    ultraGridExcelExporter1.Export(ultraGrid1, outFile);
                }
            }
        }
        public void InitUltraWinGridSetting()
        {
            this.ultraGrid1.DisplayLayout.Override.RowSelectors = Infragistics.Win.DefaultableBoolean.True;
            this.ultraGrid1.DisplayLayout.Override.RowSelectorNumberStyle = Infragistics.Win.UltraWinGrid.RowSelectorNumberStyle.VisibleIndex;
            this.ultraGrid1.DisplayLayout.Override.RowSelectorWidth = 40;
        }
        //private void ultraGrid1_InitializeLayout(object sender, InitializeLayoutEventArgs e)
        //{
        //    InitColumnWidth();
        //    InitUltraWinGridSetting();
        //    ClassUltraGridCommonSet.ultraGrid1_InitializeRowFilter(e);
        //    e.Layout.Override.RowSizing = RowSizing.Free;
        //}
        private void InitColumnWidth()
        {
            int nWidth = 20;
            ultraGrid1.DisplayLayout.Bands[0].Columns["合同号"].Width = nWidth + 95;
            ultraGrid1.DisplayLayout.Bands[0].Columns["出货日期"].Width = nWidth + 80;
            ultraGrid1.DisplayLayout.Bands[0].Columns["柜号"].Width = nWidth + 95;
            ultraGrid1.DisplayLayout.Bands[0].Columns["报关金额"].Width = nWidth + 80;
            ultraGrid1.DisplayLayout.Bands[0].Columns["币种"].Width = nWidth + 70;
            ultraGrid1.DisplayLayout.Bands[0].Columns["结汇日期"].Width = nWidth + 70;

            ultraGrid1.DisplayLayout.Bands[0].Columns["已结汇"].Width = nWidth + 80;
            ultraGrid1.DisplayLayout.Bands[0].Columns["折算率"].Width = nWidth + 80;
            ultraGrid1.DisplayLayout.Bands[0].Columns["折算原币"].Width = nWidth + 70;
            ultraGrid1.DisplayLayout.Bands[0].Columns["结汇余额(USD)"].Width = nWidth + 70;
        }

 

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
             DataSet ds = m_bllFactoryDataTab.GetList("");
             if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
             {
                 string strNow = DateTime.Now.Date.ToShortDateString();
                 string strMonth = this.dateTimePickerTo.Value.Date.Month.ToString();
                 if (!Directory.Exists(m_strFolder + strNow + @"/"))//判断是否存在
                 {
                     Directory.CreateDirectory(m_strFolder + strNow + @"/");//创建新路径
                 }

                 
                 for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                 {
                     int nID = (int)ds.Tables[0].Rows[i]["FactoryDataTabID"];
                     string strFacName = ds.Tables[0].Rows[i]["工厂简称"].ToString();
                     if (strFacName == "")
                         continue;
                     m_nFacId = nID;
                     ShowAllRecordInDataGridViewBatch(m_nFacId.ToString());
                     DataTable dt = (DataTable)ultraGrid1.DataSource;

                     if (dt != null)
                     {
                         if (dt.Rows.Count > 0)
                         {
                             string outFile = m_strFolder + strNow + @"/" + strFacName + strMonth + "月对账单.xls";
                             ultraGridExcelExporter1.Export(ultraGrid1, outFile);
                         }
                     }

                     System.Threading.Thread.Sleep(2000);

                 }
             }
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            MessageBox.Show("导出全部工厂对账单完成!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }

        private void button1_Click(object sender, EventArgs e)
        {
            ultraGrid1.Visible = false;
            backgroundWorker1.RunWorkerAsync();
        }

        private void ultraGridExcelExporter1_CellExported(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.CellExportedEventArgs e)
        {

        }

        private void ultraGrid1_InitializeLayout(object sender, InitializeLayoutEventArgs e)
        {
            InitColumnWidth();
            InitUltraWinGridSetting();
            ClassUltraGridCommonSet.ultraGrid1_InitializeRowFilter(e);
            //ultraGrid1.DisplayLayout.Bands[0].UseRowLayout = true;
            //ultraGrid1.DisplayLayout.Override.RowSizing = RowSizing.AutoFree;
            e.Layout.Override.RowSizing = RowSizing.AutoFree;
            e.Layout.Override.CellAppearance.TextVAlign = VAlign.Middle;
        }
    }
}
